﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Globalization;
using EnetaMvc.Core.Domain.SLA;
using EnetaMvc.ApplicationServices.Dto;

namespace EnetaMvc.ApplicationServices.SlaLogsDataExporter
{
    public class SlaLogsExcelExporter //: IDataExporter<EmployeeDataDto>
    {
        //private readonly IJobFamilyService _jobFamilyService;
        //private readonly ILogger _logger;
        private string _currentLanguage;
        private SpreadsheetDocument _sheetDoc;

        public SlaLogsExcelExporter()
        {
            //_jobFamilyService = ObjectFactory.GetInstance<IJobFamilyService>();
            //_logger = ObjectFactory.GetInstance<ILogger>();
            _currentLanguage = Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName;
        }

        public IDictionary<string, string> SupportedFileFormats
        {
            get 
            {
                return new Dictionary<string, string>
                {
                    { "xlsx", "Excel 2007" }
                };
            }
        }

        public void Export(Stream stream, string format, IEnumerable<SlaMonitoringLogDto> data)
        {
            var ms = new MemoryStream();

            using (_sheetDoc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = _sheetDoc.AddWorkbookPart();
                var sheetPart = workbookPart.AddNewPart<WorksheetPart>();
                var workbook = new Workbook();
                var worksheet = new Worksheet();
                var sheetData = new SheetData();

                InitializeStyles(_sheetDoc, workbookPart, sheetPart, workbook, worksheet, sheetData);

                _sheetDoc.WorkbookPart.Workbook = workbook;
                _sheetDoc.WorkbookPart.Workbook.Save();

                AddHeaderRow(sheetData);

                var i = 0;
                foreach (var dataUnit in data)
                {
                    i++;
                    AddEmployeeDataRow(sheetData, i, dataUnit);
                }
                worksheet.Save();

                _sheetDoc.Close();
                ms.Seek(0, SeekOrigin.Begin);
                ms.CopyTo(stream);
            }
            ms.Dispose();
        }

        private void AddHeaderRow(SheetData sheetData)
        {
            Row header = new Row();

            header.RowIndex = Convert.ToUInt32(sheetData.ChildElements.Count()) + 1;
            sheetData.Append(header);

            AppendCell(header, header.RowIndex, "ID", 1);
            AppendCell(header, header.RowIndex, "LogName", 1);
            AppendCell(header, header.RowIndex, "PreviousPublishStateUser", 1);
            AppendCell(header, header.RowIndex, "PreviousPublishState", 1);
            AppendCell(header, header.RowIndex, "PreviousStateDate", 1);
            AppendCell(header, header.RowIndex, "User", 1);
            AppendCell(header, header.RowIndex, "NewStateDate", 1);
            AppendCell(header, header.RowIndex, "NewPublishState", 1);
        }

        private void AddEmployeeDataRow(SheetData sheetData, int rowNo, SlaMonitoringLogDto dto)
        {
            Row header = new Row();

            // increment the row index to the next row
            header.RowIndex = Convert.ToUInt32(sheetData.ChildElements.Count()) + 1;
            sheetData.Append(header);

            AppendCell(header, header.RowIndex, dto.Id, 0);
            AppendCell(header, header.RowIndex, dto.LogName, 0);
            AppendCell(header, header.RowIndex, dto.PreviousPublishStateUser, 0);
            AppendCell(header, header.RowIndex, dto.PreviousPublishState, 0);
            AppendCell(header, header.RowIndex, dto.PreviousStateDate, 0);
            AppendCell(header, header.RowIndex, dto.User, 0);
            AppendCell(header, header.RowIndex, dto.NewPublishState, 0);
            AppendCell(header, header.RowIndex, dto.NewStateDate, 0);
        }

        private void InitializeStyles(SpreadsheetDocument doc, WorkbookPart workbookPart, WorksheetPart sheetPart, Workbook workbook, Worksheet worksheet, SheetData sheetData)
        {
            var stylesPart = doc.WorkbookPart.AddNewPart<WorkbookStylesPart>();
            stylesPart.Stylesheet = GenerateStyleSheet();
            stylesPart.Stylesheet.Save();

            var sharedStringTablePart = doc.WorkbookPart.AddNewPart<SharedStringTablePart>();
            sharedStringTablePart.SharedStringTable = new SharedStringTable();
            sharedStringTablePart.SharedStringTable.Save();

            worksheet.Append(sheetData);
            sheetPart.Worksheet = worksheet;
            sheetPart.Worksheet.Save();

            var sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "SLA Log";
            sheet.SheetId = 1;
            sheet.Id = workbookPart.GetIdOfPart(sheetPart);
            sheets.Append(sheet);
            workbook.Append(sheets);
        }

        private Stylesheet GenerateStyleSheet()
        {
            var cellStyleFormats = new CellStyleFormats(
                    new CellFormat() { FontId = 0, FillId = 0, BorderId = 0, NumberFormatId = 49 }                         // Index 0 - The default cell style.  If a cell does not have a style index applied it will use this style combination instead
                //new CellFormat() { FontId = 0, FillId = 0, BorderId = 0, NumberFormatId = 49, ApplyFont = true },     // Index 1 - Bold
                //new CellFormat() { FontId = 0, FillId = 0, BorderId = 0, NumberFormatId = 0, ApplyNumberFormat = true }
                );
            cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);

            var fonts = new Fonts(
                    new Font(                                                               // Index 0 - The default font.
                        new FontSize() { Val = 11 },
                        new Color() { Rgb = new HexBinaryValue() { Value = "FF000000" } },
                        new FontName() { Val = "Calibri" }
                    )
                );
            fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
            fonts.KnownFonts = BooleanValue.FromBoolean(true);

            var fills = new Fills(
                    new Fill(new PatternFill() { PatternType = PatternValues.None }),
                    new Fill(new PatternFill() { PatternType = PatternValues.Gray125 })
                );
            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);

            var borders = new Borders(
                    new Border(                                                         // Index 0 - The default border.
                        new LeftBorder(),
                        new RightBorder(),
                        new TopBorder(),
                        new BottomBorder(),
                        new DiagonalBorder()
                    )
                );
            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);

            var numFormats = new NumberingFormats(
                    new NumberingFormat
                    {
                        NumberFormatId = UInt32Value.FromUInt32(3),
                        FormatCode = StringValue.FromString("0.00")
                    }
                );
            numFormats.Count = UInt32Value.FromUInt32((uint)numFormats.ChildElements.Count);

            var cellFormats = new CellFormats(
                    new CellFormat() { FontId = 0, FillId = 0, BorderId = 0, NumberFormatId = 49 },                          // Index 0 - The default cell style.  If a cell does not have a style index applied it will use this style combination instead
                    new CellFormat() { FontId = 0, FillId = 0, BorderId = 0, NumberFormatId = 49, ApplyFont = true },     // Index 1 - Bold
                    new CellFormat() { FontId = 0, FillId = 0, BorderId = 0, NumberFormatId = 0, ApplyNumberFormat = true }
                );
            foreach (var fmt in cellFormats)
                fmt.SetAttribute(new OpenXmlAttribute { LocalName = "xfId", Value = "0" });
            cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);

            var css = new CellStyles();
            var cs = new CellStyle
            {
                Name = StringValue.FromString("Normal"),
                FormatId = 0,
                BuiltinId = 0
            };
            css.Append(cs);
            css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);

            var dfs = new DifferentialFormats { Count = 0 };

            var tss = new TableStyles
            {
                Count = 0,
                DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
                DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
            };

            var style = new Stylesheet(
                numFormats,
                fonts,
                fills,
                borders,
                cellStyleFormats,
                cellFormats,
                css,
                dfs,
                tss
            );
            //style.ClearAllAttributes();
            //style.AddNamespaceDeclaration("", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
            style.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            //style.AddNamespaceDeclaration("mc:Ignorable", "x14ac");
            style.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
            // set xmlns as attribute!!!!
            style.SetAttribute(new OpenXmlAttribute("xmlns", "", "http://schemas.openxmlformats.org/spreadsheetml/2006/main"));
            style.SetAttribute(new OpenXmlAttribute("ms:Ignorable", "http://schemas.openxmlformats.org/markup-compatibility/2006", "x14ac"));
            //foreach (var d in style.GetAttributes())
            //    _logger.Debug("ATTR: " + d.XmlQualifiedName.Name + " " + d.Value);
            return style;
        }

        public string GetMimeTypeForExtension(string extension)
        {
            if (string.IsNullOrEmpty(extension))
                return string.Empty;

            if (extension.ToLower() == "xlsx")
                return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            return string.Empty;
        }

        public bool AddSharedString(string stringItem, bool save = true)
        {
            SharedStringTable sharedStringTable = _sheetDoc.WorkbookPart.SharedStringTablePart.SharedStringTable;

            if (0 == sharedStringTable.Where(item => item.InnerText == stringItem).Count())
            {
                sharedStringTable.AppendChild(
                   new DocumentFormat.OpenXml.Spreadsheet.SharedStringItem(
                      new DocumentFormat.OpenXml.Spreadsheet.Text(stringItem)));

                // Save the changes
                if (save)
                {
                    sharedStringTable.Save();
                }
            }

            return true;
        }

        public int IndexOfSharedString(string stringItem)
        {
            SharedStringTable sharedStringTable = _sheetDoc.WorkbookPart.SharedStringTablePart.SharedStringTable;
            bool found = false;
            int index = 0;

            foreach (SharedStringItem sharedString in sharedStringTable.Elements<SharedStringItem>())
            {
                if (sharedString.InnerText == stringItem)
                {
                    found = true;
                    break; ;
                }
                index++;
            }

            return found ? index : -1;
        }




        


     

        public void AddRow(Worksheet currentWorkSheet, List<object> dataItems)
        {
            // Find the sheetdata of the worksheet
            SheetData sd = (SheetData)currentWorkSheet.Where(x => x.LocalName == "sheetData").First();
            Row header = new Row();

            // increment the row index to the next row
            header.RowIndex = Convert.ToUInt32(sd.ChildElements.Count()) + 1;
            sd.Append(header);

            foreach (string item in dataItems)
            {
                AppendCell(header, header.RowIndex, item, 0);
            }

            // save worksheet
            currentWorkSheet.Save();
        }

        private void AppendCell(Row row,  uint rowIndex, object value, uint styleIndex)
        {
            Cell cell = new Cell();

            if (value == null)
            {
                cell.CellValue = new CellValue(String.Empty);
                cell.StyleIndex = styleIndex;
                cell.DataType = CellValues.String;
            }
            else if (value is decimal)
            {
                cell.CellValue = new CellValue(((decimal)value * 1.00m).ToString("0.00", new CultureInfo("en-US")));
                cell.StyleIndex = 2;    //styleindex for number
                cell.DataType = CellValues.Number;
            }
            else if (value is int || value is uint || value is short)
            {
                cell.CellValue = new CellValue(value.ToString());
                cell.StyleIndex = 2;    //styleindex for number
                cell.DataType = CellValues.Number;
            }
            else    //string
            {
                cell.StyleIndex = styleIndex;  // Style index comes from stylesheet generated in GenerateStyleSheet()
                cell.DataType = CellValues.SharedString;

                string valueStr = value.ToString();
                if (IndexOfSharedString(valueStr) == -1)
                    AddSharedString(valueStr);

                int valueIndex = IndexOfSharedString(valueStr);
                cell.Append(new CellValue(valueIndex.ToString()));
            }


            //Get the last cell's column
            string nextCol = "A";
            Cell c = (Cell)row.LastChild;
            if (c != null) // if there are some cells already there...
            {
                int numIndex = c.CellReference.ToString().IndexOfAny(new char[] { '1', '2', '3', '4', '5', '6', '7', '8', '9' });
                // Get the last column reference
                string lastCol = c.CellReference.ToString().Substring(0, numIndex);
                // Increment
                nextCol = IncrementColRef(lastCol);
            }

            cell.CellReference = nextCol + rowIndex;
            row.AppendChild(cell); 
        }

        private string IncrementColRef(string lastRef)
        {
            char[] characters = lastRef.ToUpperInvariant().ToCharArray();
            int sum = 0;

            for (int i = 0; i < characters.Length; i++)
            {
                sum *= 26;
                sum += (characters[i] - 'A' + 1);
            }

            sum++;
            string columnName = string.Empty;
            int modulo;

            while (sum > 0)
            {
                modulo = (sum - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                sum = (int)((sum - modulo) / 26);
            }

            return columnName;
        }
    }
}
